<?php

namespace app\admin\service;

use app\admin\controller\Excel;
use app\admin\model\User;
use app\common\BaseHelper;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use app\admin\model\Department;
use think\Db;

class Personnel
{

    /**
     * 下载Excel模板
     */
    public static function downloadTemplate($request){
        $xlsTitle = 'Templete';
        $spreadsheet = new Spreadsheet();
        $spreadsheet->setActiveSheetIndex(0);
        $spreadsheet->getActiveSheet()->setTitle($xlsTitle);
        $spreadsheet->getActiveSheet()->setCellValue('A1', '工作人员基本信息(注意：导入数据时请只保留此模板的第二行作为表头（即为导入Excel的第一行），然后，从第二行开始添加有效数据。)');
        // 合并列
        $spreadsheet->getActiveSheet()->mergeCells('A1:J1');
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $worksheet = $spreadsheet->getActiveSheet();

        $index0 = 2;
        $cellArray0 = array(
            'A' => '姓名',
            'B' => '部门',
            'C' => '性别(1=男|2=女)',
            'D' => '职务',
            'E' => '手机号',
            'F' => '学历',
            'G' => '是否为党员(1=是|0=否)',
            'H' => '入党时间(党员必填项，格式：2018-08-08)',
            'I' => '登录账号',
            'J' => '初始密码',
        );
        foreach ($cellArray0 as $key0 => $cell0) {
            $spreadsheet->getActiveSheet()->setCellValue($key0 . $index0, $cell0);
            //$spreadsheet->getActiveSheet()->getColumnDimension($key0)->setWidth(10);
            //$spreadsheet->getActiveSheet()->getColumnDimension($key0)->setAutoSize(true);
        }

        $cellArray = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K'];
        $spreadsheet->getActiveSheet()->mergeCells('A4:J4');  //合并单元格
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $worksheet->getStyle('A4')->applyFromArray($styleArray);  //水平居中
        $spreadsheet->getActiveSheet()->setCellValue('A4', '填写规范');
        $spreadsheet->getActiveSheet()->getStyle('A4')->getFont()->setBold(true);
        // 部门
        $spreadsheet->getActiveSheet()->setCellValue('A5', '部门可填');
        $spreadsheet->getActiveSheet()->getStyle('A5')->getFont()->setBold(true);
        $index0 = 6;
        $Department = Department::where(true)->field('id,name')->select();
        foreach ($Department as $key0 => $cell0) {
            $spreadsheet->getActiveSheet()->setCellValue('A' . ($index0), $cell0['name']);
            $index0 ++;
        }

        // 学历
        $index30 = 6 + count($Department) + 2;
        $spreadsheet->getActiveSheet()->setCellValue('A' . ($index30-1), '学历可填');
        $spreadsheet->getActiveSheet()->getStyle('A' . ($index30-1))->getFont()->setBold(true);
        $educationList = get_education();
        $i1 = 1;
        foreach ($educationList as $key1 => $cell1) {
            $spreadsheet->getActiveSheet()->setCellValue($cellArray[$i1] . ($index30-1), $cell1);
            $i1 ++;
        }
        $filePath = ROOT_PATH . 'public' . DS . 'upload' . DS . 'csv' . DS . 'member' . DS . 'templete' . DS . date('Y-m-d');
        $fileName = $filePath . '/member_templete_' . date('y-m-d-H-i-s');

        //检查文件或目录是否存在
        if(!file_exists($filePath)){
            mkdir($filePath, 0777, true);
        }
        $writer = new Xlsx($spreadsheet);
        //$writer->save($fileName . '.xls'); // or .xlsx

        $xlsTitle = iconv('utf-8', 'gb2312', $xlsTitle); // 文件名称
        $downFileName = basename($fileName,".".pathinfo($fileName, PATHINFO_EXTENSION));
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$downFileName.xls"); // attachment 新窗口打印,inline 本窗口打印

        $writer->save('php://output');
        exit;
    }

    /**
     * 导入数据
     */
    public static function import(){
        $file = request()->file('csv_file');
        $model = new \app\admin\model\User();

        Db::startTrans();
        try {
            $filePath = ROOT_PATH . 'public' . DS . 'upload';
            $info = $file->move($filePath);
            $fileName = $info->getSaveName();
            //$fileName = 'user_' . date('y-m-d-H-i-s') . '.' . substr($file,strripos($file,'.')+1);
            //move_uploaded_file($file, iconv("UTF-8","gb2312", $filePath . DS . $fileName));
            $fileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($filePath . DS . $fileName);
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($fileType);
            // 设置编码
            if(strtolower($fileType) == 'csv'){
                $reader->setInputEncoding('CP936'); // 只对csv文件有效
            }
            $spreadsheet = $reader->load($filePath . DS . $fileName);

            $sheetData = $spreadsheet->getActiveSheet()->toArray();
            if(count($sheetData) <= 1){
                return json(['status' => 0, 'msg' => '未找到文件内容，请填写数据。']);
            }
            foreach ($sheetData as $key => $val) {
                if($key == 0){
                    continue;
                }
                if(count($val) < 10){
                    Db::rollback();
                    return json(['status' => 0, 'msg' => '文件内容不正确，请按照模板来填写内容。']);
                }

                // 必填字段 验证
                if(!$val[0] || !$val[1] || !$val[8]){
                    Db::rollback();
                    return json(['status' => 0, 'msg' => '姓名、部门、登录账号等是必填字段']);
                }
                //账号检测
                $User = User::where('username',$val[8])->find();
                if($User){
                    continue;
                }
                // 姓名
                $name = $val[0];
                //部门Id
                $departmentId = Department::where(['name'=>$val[1]])->value('id');
                // 性别
                $sex = in_array($val[2], [1, 2]) ? $val[2] : 1;
                //职务
                $duties = $val[3];
                //手机号
                $p_phone = $val[4];
                //学历
                $education = $val[5];
                // 是否为党员(1=是|2=否)
                $is_partymember = in_array($val[6], [0, 1]) ? $val[6] : 0;
                //入党日期
                $partyTime = $val[7];
                //登录账号
                $username = $val[8];
                //初始密码
                if($val[9]){
                    $pwd = BaseHelper::passwordEncrypt($val[9]);
                }else{
                    $pwd = BaseHelper::passwordEncrypt('123456');
                }
                $password = $pwd;

                $data = [
                    'id'                 => BaseHelper::getUUID(),
                    'p_name'             => $name,
                    'departmentId'       => $departmentId,
                    'sex'                => $sex,
                    'duties'             => $duties,
                    'p_phone'            => $p_phone,
                    'education'          => $education,
                    'is_partymember'     => $is_partymember,
                    'partyTime'          => $partyTime,
                    'username'      => $username,
                    'password'         => $password,
                ];

                $result = $model->isUpdate(false)->save($data);
                if($result === false){
                    Db::rollback();
                    return json(['status' => 0, 'msg' => '导入失败！']);
                }
            }
            @unlink($filePath . DS . $fileName);

            Db::commit();
            return json(['status' => 1, 'msg' => '导入成功！']);
        } catch (\Exception $e){
            Db::rollback();
            $msg = $e->getMessage();
            //$msg = '导入失败，请严格按照模板来填写内容。';
            return json(['status' => 0, 'msg' => $msg]);
        }

        return json(['status' => 0, 'msg' => $msg]);
    }

    /**
     * 导出数据
     */
    public static function export($request){
        $param = $request->param();

        $where = [
            'status' => 1,
            'type' => ['in', [0,1]]
        ];

        if(!empty($param['departmentId'])){
            $where['departmentId'] = $param['departmentId'];
        }
        if(!empty($param['p_name'])){
            $where['p_name'] = ['like', '%'.$param['p_name'].'%'];
        }

        $model = model('User');
        $data = $model->where($where)->order('createDate', 'asc')->select();
        if(count($data) <= 0){
            return [
                'code' => 2,
                'msg' => '没有数据！'
            ];
        }

        $departmentDB = db('department');
        foreach ($data as $key => $val) {
            // 所属部门
            $val['departmentId'] = $departmentDB->where(['id' => $val['departmentId']])->value('name');
            // 性别
            switch ($val['sex']) {
                case 1: $val['sex'] = '男'; break;
                case 2: $val['sex'] = '女'; break;
                default : $val['sex'] = '男'; break;
            }

            // 是否为党员
            switch ($val['is_partymember']) {
                case 1: $val['is_partymember'] = '是'; break;
                case 0: $val['is_partymember'] = '否'; break;
                default : $val['is_partymember'] = '否'; break;
            }

            // 入党时间
            if($val['partyTime']){
                $val['partyTime'] = $val['partyTime'];
            }else{
                $val['partyTime'] = '';
            }
        }
        $cellName = array(
            'A' => ['id', '编号'],
            'B' => ['p_name', '姓名'],
            'C' => ['departmentId', '部门'],
            'D' => ['sex', '性别'],
            'E' => ['duties', '职务'],
            'F' => ['p_phone', '手机号'],
            'G' => ['education', '学历'],
            'H' => ['is_partymember', '是否为党员'],
            'I' => ['partyTime', '入党时间'],
            'J' => ['username', '登录账号'],
            'K' => ['password', '初始密码'],
        );

        $filePath = ROOT_PATH . 'public' . DS . 'upload' . DS . 'csv' . DS . 'member' . DS . date('Y-m-d');
        $fileName = $filePath . '/member_' . date('y-m-d-H-i-s');

        $excel = new Excel();
        $excel->exportExcel('工作人员', $data, $cellName, $filePath, $fileName);

        return [
            'code' => 1,
            'msg' => '导出成功！'
        ];
    }

}